Before computers, numerical analysis, particularly financial ones, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organising data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of “electronic spreadsheets.” Better known simply as “spreadsheets,” these software programs provide a computerised replacement for the traditional financial modelling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what word processors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visible calculator for a user. Because this “pad” exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, a letter to each column, and another letter to each sheet (or page) of the spreadsheet. To reference a location at column A and row 1 of the second page (i.e., the upper-left hand corner), for example, the user types in “B:A1”. In this manner, the spreadsheet defines an addressable storage location or “cell” at each intersection of a row with a column within a given page.
Data entry into an electronic spreadsheet, occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or “formulas” specifying calculations to be performed on the numbers stored in spreadsheet cells. Such spreadsheet cells can also be defined and named as a range as long as they are arranged as a related set of cells. A typical example of such a named range simply corresponds to a regular table found in an accountant's pad. In this fashion, range names can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, S., Using Quattro Pro 2, Borland-OsborneIMcGraw-Mll, 1990; and LeBlond, G. and Cobb, D., Using 1-2-3, Que corp., 1985.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform “what-if” scenarios. After a set of computational relationships has been entered into a worksheet, thanks to imbedded formulas for instance, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Thus, electronic spreadsheet systems were invented to solve “what-if” problems, that is, changing an input and seeing what happens to an output. Another benefit of spreadsheet programs is also the capability of extracting statistical information out of a set of data that may for instance correspond to experimental measurements data in the field of pharmaceutical test, medical test, or even manufacturing test. As long as new measurements are available, the electronic spreadsheet as a capture and analysis tool can easily and automatically reflect the impact of the new measurements on some statistical summary information.
A common situation occurring in the electronic spreadsheet environment, is the management and handling of large tables where several samples are recorded in a plurality of fields, said samples corresponding for instance to biomedical test measurements. Each record comprises different samples—not necessarily all of them—measured at a given time. In addition a record can also contain other fields besides the sample fields, such as operational information fields. With such table, a common need is to evaluate some summary information (such as sums, means, maximums, minimums, standard deviations, etc.) in a given field, for the records fulfilling a criteria based on an order relationship.
This case is illustrated in the example shown in FIG. 3, based on a table 300 where each record 310 comprises 7 fields:
the name of the operator 301,
the temperature 302,
the “Measure#1” 303,
the “Measure#2” 304,
the “Measure#3” 305,
the “Measure#4” 306,
the “Measure#5” 307.
Depending on the records, all the measures or a subset of them are present. By convention, the records are introduced within the table 300 by adding the most recent record on the top. Therefore the bottom record 311 is the oldest one whereas the top record 312 is the most recent one.
After having filled the table 300, the spreadsheet user is looking for the following information:    Q 1: what is the average value of the 5 most recent samples of the “Measure#3” 305?    Q 2: what is the value of the most recent sample of the “Measure#1” 303?    Q 3: what is the value of the sample of the “Measure#5” 307 for the median sample of the “Measure#2” 304?    Q 4: What is the minimum value of the samples of the “Measure#4” 306 for the four least (less) recent samples of the “Measure#3” 305 with a value greater than 11.5 ?
The above 4 examples, combine, in a same question, a conventional criteria based on a field value, with a new type of criteria based on an order relationship. According to the present invention, it is also possible to combine, in a same question, several conventional criterias with several criterias based on an order relationship. This more complex combination of criterias is a quite valid and valuable requirement also addressed by the present invention. Such combination will not be further detailed in the description since it would only add complexity, at the expense of clarity.
These questions cannot be answered today with the conventional means available in electronic spreadsheet environments because the built-in statistical or database functions do not allow to take into account a criteria based on an order relationship such as:
the 5 last samples of a field
the last sample of a field
the median sample of a field
The 4 first samples of a field
Indeed the conventional database functions available in electronic spreadsheet environments, allow the spreadsheet user to define criterias which are only based on the value taken by selected fields. Such database functions are quite powerful. They allow the spreadsheet user to automatically extract statistical information out of a potentially huge amount of data, while specifying through specific criterias the conditions to be fulfilled by the records contributing to the computation of the statistical information. Such database functions, as available in the Excel spreadsheet product from Microsoft Corporation, are for instance the following:                DAVERAGE (for computing field average for records matching a condition), DCOUNT (for counting records matching a condition),        DMAX (for returning the highest field value for records matching a condition),        DMIN (for returning the smallest field value for records matching a condition),        DPRODUCT (for returning the multiplication of field values for records matching a condition),        DSTDEV (for returning the field standard deviation for records matching a condition),        DSUM (for returning the sum of field values for records matching a condition),        DVAR (for returning the field variance for records matching a conditions).        
Referring to FIG. 3, a @DAVG database function available in the 123 spreadsheet tool of Lotus Corporation, can for instance automatically provide the average temperature for the records where the operator is “frederic” according to the formula @DAVG(B2 . . . H25, “temperature”, operator=“frederic”). In this example, the criteria operator=“frederic” relies on the value of the operator field 301. There is no conventional means available to specify a criteria based on an order relationship.
As a result, an electronic spreadsheet user, who needs to evaluate summary information, based on order oriented criteria, would have:                either to do part of the computation by himself (for instance the question Q1 can be answered by specifying the formula @AVG(F3, F6, F8 . . . F10) after having “manually” identified that the cells F3, F6 and F8 . . . F10 correspond to the last 5 samples for the “Measure#3” 305,        or do all the computation by himself (for instance the question Q2 can be answered by determining that the last sample of the “Measure#1” 303 has a value equal to 0.00707).In both cases, the information is not fully automated, and is only valid for the current state of table 300. Indeed if a new record 312 is added at the top of table 300, with all the measurement fields populated, then the questions Q1 to Q4 will receive different answers.        
The proposed invention offers an innovative solution to this problem by introducing an Order Oriented Criteria (OOC) for formally specifying a set of conditions based on order relationships, so that the computation of summary information such as the ones formulated in questions Q1 to Q4, gets fully automated.